package com.hz.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hz.dbconn.DBConnect;
import com.hz.model.ParamSpecial;


public class ParamSpecialManage {
	
	/**
	 * 返回某学院的最大学制
	 * @param departmentId
	 * @return
	 */
	public static int getMaxXuezhi(int departmentId)
	{
		int maxxuezhi = 0;
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		String sql = "select max(xuezhi) from param_special where departmentId=" + departmentId;
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			if(rs.next()) 
			{
				maxxuezhi = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
		return maxxuezhi;
	}
	
	/**
	 * 录入专业
	 * 
	 * @param special
	 *
	 */
	public static boolean addSpecial(ParamSpecial special)
	{
		DBConnect dbc=new DBConnect();
		String sql="insert into param_special(specialName,departmentId,specialKind,isNormal,xuezhi,level) values(?,?,?,?,?,?)";	
		dbc.prepareStatement(sql);
		try
		{
			dbc.setString(1, special.getSpecialName());
			dbc.setInt(2, special.getDepartmentId());
			dbc.setString(3, special.getSpecialKind());
			dbc.setString(4,special.getIsNormal());
			dbc.setInt(5, special.getXuezhi());
			dbc.setString(6,special.getEduLevel());
			dbc.executeUpdate();
			return true;
			
		}catch(SQLException e)
		{
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		
	}
	public static boolean modifySpecial(int id,ParamSpecial special)
	{
		DBConnect dbc = new DBConnect();
		String sql = "select * from param_special where id = " + id;
		dbc.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		try {
			ResultSet rs = dbc.executeQuery();
			if (rs.next()) {
				rs.updateString("specialName", special.getSpecialName());
				rs.updateInt("departmentId", special.getDepartmentId());	
				rs.updateString("specialKind", special.getSpecialKind());
				rs.updateString("isNormal", special.getIsNormal());	
				rs.updateInt("xuezhi", special.getXuezhi());
				rs.updateString("level", special.getEduLevel());
				rs.updateRow();
				// 关闭记录集
				dbc.close(rs);
			} else {
				System.out.println("记录不存在!");
			}
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	

	//还未添加对专业表的维护操作
	public static List<ParamSpecial> getSpecialByDepartment(int departmentId) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		List<ParamSpecial> list = new ArrayList<ParamSpecial>();
		String sql = "select * from param_special where departmentId=" + departmentId;
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				ParamSpecial ps = new ParamSpecial();
				ps.setId(rs.getInt("id"));
				ps.setSpecialName(rs.getString("specialName"));
				ps.setDepartmentId(rs.getInt("departmentId"));
				ps.setSpecialKind(rs.getString("specialKind"));
				ps.setIsNormal(rs.getString("isNormal"));
				ps.setXuezhi(rs.getShort("xuezhi"));
				ps.setEduLevel(rs.getString("level"));
				list.add(ps);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	
	public static List<ParamSpecial> getSpecialByDepartment(String departmentNum) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		List<ParamSpecial> list = new ArrayList<ParamSpecial>();
		String sql = "select * from param_special where departmentId='" + departmentNum+"'";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				ParamSpecial ps = new ParamSpecial();
				ps.setId(rs.getInt("id"));
				ps.setSpecialName(rs.getString("specialName"));
				ps.setDepartmentId(rs.getInt("departmentId"));
				ps.setSpecialKind(rs.getString("specialKind"));
				ps.setIsNormal(rs.getString("isNormal"));
				ps.setXuezhi(rs.getShort("xuezhi"));
				ps.setEduLevel(rs.getString("level"));
				list.add(ps);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	/**
	 * 根据学员ID和本科还是研究生，获得该学院相应ID
	 * @param departmentNum
	 * @return
	 */
	public static List<ParamSpecial> getSpecialByDepartmentAndEduLevel(String departmentNum,int eduLevel) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		List<ParamSpecial> list = new ArrayList<ParamSpecial>();
		String sql = "select * from param_special where departmentId='" + departmentNum+"' and level = '"+eduLevel+"'";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				ParamSpecial ps = new ParamSpecial();
				ps.setId(rs.getInt("id"));
				ps.setSpecialName(rs.getString("specialName"));
				ps.setDepartmentId(rs.getInt("departmentId"));
				ps.setSpecialKind(rs.getString("specialKind"));
				ps.setIsNormal(rs.getString("isNormal"));
				ps.setXuezhi(rs.getShort("xuezhi"));
				ps.setEduLevel(rs.getString("level"));
				list.add(ps);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	
	public static List<ParamSpecial> getSpecialByEduLevel(int eduLevel) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		List<ParamSpecial> list = new ArrayList<ParamSpecial>();
		String sql = "select * from param_special where level = '"+eduLevel+"'";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				ParamSpecial ps = new ParamSpecial();
				ps.setId(rs.getInt("id"));
				ps.setSpecialName(rs.getString("specialName"));
				ps.setDepartmentId(rs.getInt("departmentId"));
				ps.setSpecialKind(rs.getString("specialKind"));
				ps.setIsNormal(rs.getString("isNormal"));
				ps.setXuezhi(rs.getShort("xuezhi"));
				ps.setEduLevel(rs.getString("level"));
				list.add(ps);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	//还未添加对专业表的维护操作
	public static List<ParamSpecial> getSpecialByDepartmentEduLevel(String departmentNum,String eduLevel) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		List<ParamSpecial> list = new ArrayList<ParamSpecial>();
        
		if(departmentNum==null || departmentNum.equals(""))
			departmentNum="%";
		if(eduLevel==null || eduLevel.equals("")||eduLevel.equals("1"))
			eduLevel="%";
		
		String sql = "select * from param_special where departmentId like'" + departmentNum+"' and level like'"+eduLevel+"'";
		System.out.println("pp"+sql);
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				ParamSpecial ps = new ParamSpecial();
				ps.setId(rs.getInt("id"));
				ps.setSpecialName(rs.getString("specialName"));
				ps.setDepartmentId(rs.getInt("departmentId"));
				ps.setSpecialKind(rs.getString("specialKind"));
				ps.setIsNormal(rs.getString("isNormal"));
				ps.setXuezhi(rs.getShort("xuezhi"));
				ps.setEduLevel(rs.getString("level"));
				list.add(ps);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	
	public static List<ParamSpecial> getSpecialByDepartmentEduLevel(String departmentNum) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		List<ParamSpecial> list = new ArrayList<ParamSpecial>();
		
		if(departmentNum==null || departmentNum.equals(""))
			departmentNum="%";

		
		String sql = "select * from param_special where departmentId like'" + departmentNum+"'";
		System.out.println("pp"+sql);
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				ParamSpecial ps = new ParamSpecial();
				ps.setId(rs.getInt("id"));
				ps.setSpecialName(rs.getString("specialName"));
				ps.setDepartmentId(rs.getInt("departmentId"));
				ps.setSpecialKind(rs.getString("specialKind"));
				ps.setIsNormal(rs.getString("isNormal"));
				ps.setXuezhi(rs.getShort("xuezhi"));
				ps.setEduLevel(rs.getString("level"));
				list.add(ps);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	public static ParamSpecial getSpecialById(int specialId) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ParamSpecial ps = new ParamSpecial();
		String sql = "select * from param_special where id=" + specialId +"";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				ps.setId(rs.getInt("id"));
				ps.setSpecialName(rs.getString("specialName"));
				ps.setDepartmentId(rs.getInt("departmentId"));
				ps.setSpecialKind(rs.getString("specialKind"));
				ps.setIsNormal(rs.getString("isNormal"));
				ps.setXuezhi(rs.getShort("xuezhi"));
				ps.setEduLevel(rs.getString("level"));
			}
			return ps;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}

	/**
	 * 查询研究生专业
	 * @param specialId
	 * @return
	 */
	public static ParamSpecial getSpecialByIdByEdulevel(int specialId) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ParamSpecial ps = new ParamSpecial();
		String sql = "select * from param_special where id=" + specialId +" and level = 1";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				ps.setId(rs.getInt("id"));
				ps.setSpecialName(rs.getString("specialName"));
				ps.setDepartmentId(rs.getInt("departmentId"));
				ps.setSpecialKind(rs.getString("specialKind"));
				ps.setIsNormal(rs.getString("isNormal"));
				ps.setXuezhi(rs.getShort("xuezhi"));
				ps.setEduLevel(rs.getString("level"));
			}
			return ps;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	public static ParamSpecial getSpecialByName(String specialName) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ParamSpecial ps = new ParamSpecial();
		String sql = "select * from param_special where specialName='" + specialName+"'";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				ps.setId(rs.getInt("id"));
				ps.setSpecialName(rs.getString("specialName"));
				ps.setDepartmentId(rs.getInt("departmentId"));
				ps.setSpecialKind(rs.getString("specialKind"));
				ps.setIsNormal(rs.getString("isNormal"));
				ps.setXuezhi(rs.getShort("xuezhi"));
				ps.setEduLevel(rs.getString("level"));
			}
			return ps;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
	}
	/**
	 * 通过专业编号获取学院编号
	 * @param special
	 * @return
	 */
	public static int getDepartmentIdBySpecialId(int special){
		int depId =0;
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		String sql = "select departmentId from param_special where id=" + special+"";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			if(rs.next()) {
				depId=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
		return depId;
	}
	
	public static int getXueZhiBySpecialId(int special){
		int xuezhi =0;
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		String sql = "select xuezhi from param_special where id=" + special+"";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			if(rs.next()) {
				xuezhi=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			// 在这里一定要注意管理数据库连接
			dbc.close();
		}
		return xuezhi;
	}
}
